#!/usr/bin/env python
# -*- coding: utf-8 -*-

from utils import log

from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, Float, Boolean, String, Unicode, UnicodeText, \
    DateTime, desc
from sqlalchemy.sql.sqltypes import TIMESTAMP
from sqlalchemy.sql import func
from controls.data_pool_mysql import engine

metadata = MetaData()
Session = sessionmaker()
Session.configure(bind=engine)

"""journey_base_item collection has
    userid:         用户id（int）
    journeyid:         游记id（非全局）（int）
    title:             游记title（string）
    utime:             游记上传时间（datetime）
"""

journey_base_table_dict = {}


class JourneyBaseTables(object):
    def make_table(self, p_cityid):
        if journey_base_table_dict.has_key(p_cityid):
            return journey_base_table_dict[p_cityid]

        class JourneyBaseItem(object):
            def __init__(self, p_cityid):
                self.p_cityid = p_cityid

            def to_dict(self):
                res = {}
                res['userid'] = self.userid
                res['journeyid'] = self.journeyid
                res['title'] = self.title
                res['utime'] = self.utime
                return res

            def __repr__(self):
                return "%d" % (self.journeyid)

        metadata = MetaData()
        journey_base_item_table = Table(
            'journey_base_' + str(p_cityid), metadata,
            Column('id', Integer, primary_key=True),
            Column('userid', Integer, index=True, nullable=False),
            Column('journeyid', Integer, index=True, nullable=False),
            Column('title', Unicode(65), index=True, nullable=False),
            Column('utime', TIMESTAMP, server_default=func.now()))
        mapper(JourneyBaseItem, journey_base_item_table)
        table = {'metadata': metadata,
                 'table': journey_base_item_table,
                 'class': JourneyBaseItem}
        journey_base_table_dict[p_cityid] = table
        return table


def create_journey_base_item_table_if_not_exist(p_cityid):
    table = JourneyBaseTables().make_table(p_cityid)
    table['metadata'].create_all(engine)


def add_journey_base_item(p_cityid, p_item):
    table = JourneyBaseTables().make_table(p_cityid)
    item = table['class'](p_cityid)
    item.userid = p_item['userid']
    item.journeyid = p_item['journeyid']
    item.title = p_item['title']
    item.utime = p_item['utime']
    try:
        session = Session()
        session.add(item)
        session.flush()
        session.commit()
        return True
    except Exception, e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def get_journey_base_except_users(p_cityid, p_userids):
    try:
        session = Session()
        table = JourneyBaseTables().make_table(p_cityid)
        # item = session.query().filter(~JourneyBaseItem.userid.in_(p_userids)).order_by(desc(JourneyBaseItem.utime)).first()
        item = session.query(table['class'].userid, table['class'].journeyid, table['class'].title, table['class'].utime
                             ).filter(~table['class'].userid.in_(p_userids)).order_by(
            desc(table['class'].utime)).first()
        if not item:
            return None
        res = dict(zip(('userid', 'journeyid', 'title', 'utime'), item))
        res['utime'] = res['utime'].strftime("%Y-%m-%d %H:%M:%S")
        return res
    except Exception, e:
        print e
        log.exp(e)
        return None
    finally:
        session.close()
        # session.remove()


def get_journey_base_all_by_cityid_userid_journeyid(p_cityid, p_userid, p_journeyid):
    try:
        session = Session()
        table = JourneyBaseTables().make_table(p_cityid)
        item = session.query(table['class']).filter_by(userid=p_userid, journeyid=p_journeyid).first()
        res = {
            'userid': item.userid,
            'journeyid': item.journeyid,
            'title': item.title,
            'utime': item.utime,
        }
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()
        # session.remove()


def get_journeyid_utime_by_cityid_userid(p_cityid, p_userid):
    try:
        session = Session()
        table = JourneyBaseTables().make_table(p_cityid)
        item = session.query(table['class']).filter_by(userid=p_userid).first()
        res = item.to_dict() if item else {}
        return res
    except Exception, e:
        log.exp(e)
        return None
    finally:
        session.close()


def delete_journey_by_userid(p_cityid, p_userid):
    try:
        session = Session()
        table = JourneyBaseTables().make_table(p_cityid)
        item = session.query(table['class']).filter_by(userid=p_userid).delete()
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


if __name__ == "__main__":
    '''
    test_city = 1
    create_journey_base_item_table_if_not_exist(1)
    test_item = {'userid': 4,
                 'journeyid': 4,
                 'title': 'test'}
    add_journey_base_item(test_city, test_item)
    print get_journey_base_all_by_cityid_userid_journeyid( 1, 114, 1 )
    '''
    print get_journeyid_utime_by_cityid_userid(1, 111)
    # print get_journey_base_except_users(1, [111, 112])
